package com.yuda.afterexamscore.controller;

import com.yuda.afterexamscore.service.IRankXService;
import com.yuda.common.core.web.domain.AjaxResult;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cloud.context.config.annotation.RefreshScope;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 计算RankX控制器
 */

@RestController
@RequestMapping("/rank")
@Api("计算rank排名")
@RefreshScope
public class RankXController {


    @Autowired
    private JdbcTemplate jdbcTemplate;


    @Autowired
    private IRankXService rankXService;
    /**
     * 学校排名
     * @return
     */

    @ApiOperation("计算dp_subject_score的rank_x")
    @PostMapping("/rankX/dpSubjectScore")
    public AjaxResult dpSubjectScoreRankX() {
//
//       // List<Map<String, Object>> allSchoolAndSubject = rankXService.getAllSchoolAndSubject();

        final List<Map<String,Object>> allStuSumScoreList = new ArrayList<>();

        String sql2 = "SELECT school_id,subject_relation_id FROM dp_subject_score where  subject_id != 1013 and subject_id != 1014  GROUP BY school_id,subject_relation_id ";
        jdbcTemplate.query(sql2, new Object[] {},
                new RowCallbackHandler() {
                    @Override
                    public void processRow(ResultSet rs) throws SQLException {
                        Map<String,Object> map = new HashMap<>();
                        map.put("subjectRelationId",rs.getInt("subject_relation_id"));
                        map.put("schoolId",rs.getString("school_id"));
                        allStuSumScoreList.add(map);
                    }
                });
        String updateSql = " UPDATE    dp_subject_score a INNER JOIN                                                                                                                                                               "
                + " (                                                                                                                                                                                                    "
                + " SELECT zkz_num,  score, subject_relation_id, pg_type,school_id, rank() OVER(order by score+0 desc) as `rank` from dp_subject_score where subject_relation_id = ? AND  school_id = ?  "
                + "  ) b                                                                                                                                                                                                 "
                + " ON a.score =b.score AND  a.zkz_num = b.zkz_num                                                                                                                                                        "
                + "  SET a.rank_x = b.`rank`  WHERE   a.subject_relation_id = b.subject_relation_id  AND a.school_id = b.school_id";
        jdbcTemplate.batchUpdate(updateSql, new BatchPreparedStatementSetter() {
            @Override
            public int getBatchSize() {
                return allStuSumScoreList.size();
            }

            @Override
            public void setValues(PreparedStatement ps, int i)
                    throws SQLException {
                Map<String,Object> map = allStuSumScoreList.get(i);
                ps.setObject(1,map.get("subjectRelationId"));
                ps.setObject(2,map.get("schoolId"));
            }
        });
        return AjaxResult.success();
    }


    @ApiOperation("计算dp_subject_score的rank_x")
    @PostMapping("/rankP/dpSubjectScore")
    public AjaxResult dpSubjectScoreRankP() {
        final List<Map<String,Object>> allStuSumScoreList = new ArrayList<>();
        String sql2 = "SELECT province_id,subject_relation_id FROM dp_subject_score where  subject_id != 1013 and subject_id != 1014  GROUP BY province_id,subject_relation_id";
        jdbcTemplate.query(sql2, new Object[] {},
                new RowCallbackHandler() {
                    @Override
                    public void processRow(ResultSet rs) throws SQLException {
                        Map<String,Object> map = new HashMap<>();
                        map.put("subjectRelationId",rs.getInt("subject_relation_id"));
                        map.put("provinceId",rs.getString("province_id"));
                        allStuSumScoreList.add(map);
                    }
                });
        String updateSql = " UPDATE    dp_subject_score a INNER JOIN                                                                                                                                                               "
                + " (                                                                                                                                                                                                    "
                + " SELECT zkz_num,  score, subject_relation_id, pg_type,province_id, rank() OVER(order by score+0 desc) as `rank` from dp_subject_score where subject_relation_id = ? AND  province_id = ?  "
                + "  ) b                                                                                                                                                                                                 "
                + " ON  a.zkz_num = b.zkz_num                                                                                                                                                        "
                + "  SET a.rank_p = b.`rank`  WHERE   a.subject_relation_id = b.subject_relation_id  AND a.province_id = b.province_id";
        jdbcTemplate.batchUpdate(updateSql, new BatchPreparedStatementSetter() {
            @Override
            public int getBatchSize() {
                return allStuSumScoreList.size();
            }

            @Override
            public void setValues(PreparedStatement ps, int i)
                    throws SQLException {
                Map<String,Object> map = allStuSumScoreList.get(i);
                ps.setObject(1,map.get("subjectRelationId"));
                ps.setObject(2,map.get("provinceId"));
            }
        });
        return AjaxResult.success();
    }

}
